﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonModule;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using ePlanet.ResourceRequest.Models;
using System.Data;

namespace ePlanet.ResourceRequest.DataAccess
{
    public class ResourceRequestDAL : DataHelper
    {
        public ResourceRequestDAL()
        {
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
        }

        public void AddNewResourceRequest(ResourceRequestModel requestModel)
        {
            this.InsertData(requestModel);
        }

        public void AgreeRequest(ResourceRequestModel requestModel)
        {
            if (requestModel != null && requestModel.RESOURCEREQUEST.Count > 0)
            {
                //string requestID = requestModel.RESOURCEREQUEST[0].ID;
                //DateTime validPeriod = requestModel.RESOURCEREQUEST[0].VALIDPERIOD;
                //string commentMessage = requestModel.RESOURCEREQUEST[0].COMMENTMESSAGE;
                //int canView = (int)requestModel.RESOURCEREQUEST[0].CANVIEW;
                //int canDownload = (int)requestModel.RESOURCEREQUEST[0].CANDOWNLOAD;
                //string sql = string.Format("update resourcerequest set statusID='{0}' ,validperiod='{1}',commengmessage='{2}',candownload='{3}',canview='{4}' where ID='{5}'", (int)ResourceRequestEnum.AgreeView, validPeriod, commentMessage, canDownload, canView, requestID);
                //this.DBHelper.ExecuteSql(sql);
                this.InsertData(requestModel);
            }
        }

        public void AgreeRequest(Guid id)
        {
            this.AgreeOrDisAgreeRequest(id, 1);
        }

        public void DisAgreeRequest(Guid id)
        {
            this.AgreeOrDisAgreeRequest(id, 2);
        }

        private void AgreeOrDisAgreeRequest(Guid id, int statusID)
        {
            string sql = string.Format("update resourcerequest set statusid={1} where id='{0}'", id, statusID);
            this.DBHelper.ExecuteSql(sql);
        }

        public void DisAgreeRequest(ResourceRequestModel requestModel)
        {
            if (requestModel != null && requestModel.RESOURCEREQUEST.Count > 0)
            {
                string requestID = requestModel.RESOURCEREQUEST[0].ID;
                string sql = string.Format("update resourcerequest set statusID={0} where ID='{1}'", (int)ResourceRequestEnum.DisAgreeView, requestID);
                this.DBHelper.ExecuteSql(sql);
            }
        }

        public DataTable GetRequestList(ResourceRequestEnum requestEnum, string userID)
        {
            DataTable rtnDataTable = null;
            string sql = string.Empty;
            if (requestEnum != ResourceRequestEnum.Expired)
            {
                sql = string.Format("select ID,(select username from tempuser where tempuserid=r.tempuserid) as UserName,(select datafilename from singlewell where dataid=r.welldataid) as DataFileName,r.validperiod,statusID,commentmessage from resourcerequest r where statusID={0} and tempuserID='{1}' and validperiod>sysdate", (int)requestEnum, userID);
            }
            else
            {
                sql = string.Format("select ID,welldataid,(select username from tempuser where tempuserid=r.tempuserid) as UserName,(select datafilename from singlewell where dataid=r.welldataid) as DataFileName,r.validperiod,statusID,commentmessage from resourcerequest r where validperiod<sysdate and tempuserID='{0}'", userID);
            }
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        public DataTable GetRequestListByWellDataID(ResourceRequestEnum requestEnum, string wellDataID)
        {
            DataTable rtnDataTable = null;
            string sql = string.Empty;
            if (requestEnum != ResourceRequestEnum.Expired)
            {
                //sql = string.Format("select ID,(select username from tempuser where tempuserid=r.tempuserid) as UserName,(select datafilename from singlewell where dataid=r.welldataid) as DataFileName,r.validperiod,statusID,commentmessage,candownload,canview,createtime from resourcerequest r inner join (select dataid from singlewell where wellcatalogid='{1}') t on r.welldataid=t.dataid where statusID={0} and validperiod>sysdate", (int)requestEnum, wellDataID);
                sql = string.Format("select r.id, a.orgname, a.username, b.datafilename, r.validperiod, r.statusid, r.commentmessage, r.candownload, r.canview, r.createtime from resourcerequest r inner join tempuser a   on r.tempuserid = a.tempuserid inner join singlewell b   on r.welldataid = b.dataid where r.statusid = {0}   and r.validperiod > sysdate", (int)requestEnum);
            }
            else
            {
                //sql = string.Format("select ID,(select username from tempuser where tempuserid=r.tempuserid) as UserName,(select datafilename from singlewell where dataid=r.welldataid) as DataFileName,r.validperiod,statusID,commentmessage,candownload,canview,createtime from resourcerequest r  inner join (select dataid from singlewell where wellcatalogid='{0}') t on r.welldataid=t.dataid where validperiod<sysdate", wellDataID);
                sql = string.Format("select r.id, a.orgname, a.username, b.datafilename, r.validperiod, r.statusid, r.commentmessage, r.candownload, r.canview, r.createtime from resourcerequest r inner join tempuser a   on r.tempuserid = a.tempuserid inner join singlewell b   on r.welldataid = b.dataid where   r.validperiod < sysdate");
            }
            if (!string.IsNullOrEmpty(wellDataID))
            {
                sql = string.Concat(sql, "   and b.wellcatalogid = '", wellDataID, "'");
            }
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        public DataTable GetRequestList(ResourceRequestEnum requestEnum, string userID, string wellDataID)
        {
            DataTable rtnDataTable = null;
            string sql = string.Empty;
            if (requestEnum != ResourceRequestEnum.Expired)
            {
                //允许 wellDataID 为空
                //时间：2013年3月23日 22:54:41
                if (!string.IsNullOrEmpty(wellDataID))
                {
                    //sql = string.Format(@"select ID,(select username from tempuser where tempuserid=r.tempuserid) as UserName,(select datafilename from singlewell where dataid=r.welldataid) as DataFileName,r.validperiod,statusID,commentmessage,candownload,canview,createtime from resourcerequest r inner join (select dataid from singlewell where wellcatalogid='{0}') t on r.welldataid=t.dataid where statusID={1} and validperiod>sysdate and tempuserID='{2}'", wellDataID, (int)requestEnum, userID);
                    sql = string.Format(@"select r.ID,   t.username, r.WellDataID,  s.datafilename,  r.validperiod, r.statusID,   commentmessage,  candownload,  canview,   createtime  from resourcerequest r  left join tempuser t    on t.tempuserid = r.tempuserid  left join singlewell s   on r.welldataid = s.dataid where 1 = 1   and s.wellcatalogid = '{0}'   and r.statusID = {1}   and r.validperiod > sysdate   and r.tempuserID = '{2}'", wellDataID, (int)requestEnum, userID);
                }
                else
                    //sql = string.Format(@"select ID,(select username from tempuser where tempuserid=r.tempuserid) as UserName,(select datafilename from singlewell where dataid=r.welldataid) as DataFileName,r.validperiod,statusID,commentmessage,candownload,canview,createtime from resourcerequest r inner join (select dataid from singlewell) t on r.welldataid=t.dataid where statusID={0} and validperiod>sysdate and tempuserID='{1}'",  (int)requestEnum,userID);
                    sql = string.Format(@"select r.ID,   t.username, r.WellDataID,  s.datafilename,  r.validperiod, r.statusID,   commentmessage,  candownload,  canview,   createtime  from resourcerequest r  left join tempuser t    on t.tempuserid = r.tempuserid  left join singlewell s   on r.welldataid = s.dataid where 1 = 1     and r.statusID = {0}   and r.validperiod > sysdate   and r.tempuserID = '{1}'", (int)requestEnum, userID);
                switch (requestEnum)
                {
                    case ResourceRequestEnum.NewRequest:
                    case ResourceRequestEnum.DisAgreeView:
                        sql += "  order  by r.createtime asc ";
                        break;
                    case ResourceRequestEnum.AgreeView:
                        sql += "  order  by r.validperiod asc ";
                        break;
                    case ResourceRequestEnum.Expired:
                    default:
                        break;
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(wellDataID))
                {
                    //sql = string.Format("select ID,(select username from tempuser where tempuserid=r.tempuserid) as UserName,(select datafilename from singlewell where dataid=r.welldataid) as DataFileName,r.validperiod,statusID,commentmessage,candownload,canview,createtime from resourcerequest r inner join (select dataid from singlewell where wellcatalogid='{0}') t on r.welldataid=t.dataid where validperiod>sysdate and tempuserID='{1}'", wellDataID,userID);
                    sql = string.Format(@"select r.id,       t.username,      s.datafilename,       r.validperiod,       r.statusID,       commentmessage,       candownload,       canview,       createtime  from resourcerequest r  left join tempuser t    on t.tempuserid = r.tempuserid  left join singlewell s    on r.welldataid = s.dataid  where s.wellcatalogid='{0}' and r.validperiod < sysdate   and r.tempuserID = '{1}'", wellDataID, userID);
                }
                else
                    //sql = string.Format("select ID,(select username from tempuser where tempuserid=r.tempuserid) as UserName,(select datafilename from singlewell where dataid=r.welldataid) as DataFileName,r.validperiod,statusID,commentmessage,candownload,canview,createtime from resourcerequest r inner join (select dataid from singlewell) t on r.welldataid=t.dataid where validperiod>sysdate and tempuserID='{0}'", userID);
                    sql = string.Format("select r.id,       t.username,      s.datafilename,       r.validperiod,       r.statusID,       commentmessage,       candownload,       canview,       createtime  from resourcerequest r  left join tempuser t    on t.tempuserid = r.tempuserid  left join singlewell s    on r.welldataid = s.dataid  where r.validperiod < sysdate   and r.tempuserID = '{0}'", userID);
            }

            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        public DataTable GetDone(string userID, string wellTypeID)
        {
            string sql = string.Format(@"select ID,
       (select username from tempuser where tempuserid = r.tempuserid) as UserName,
       (select datafilename from singlewell where dataid = r.welldataid) as DataFileName,
       r.validperiod,
       statusID,
       commentmessage,
       candownload,
       canview,
       createtime
  from resourcerequest r
 inner join (select dataid from singlewell where wellcatalogid = '{0}') t
    on r.welldataid = t.dataid
    and r.statusID<>0", wellTypeID);

            //added by jiejiep
            //支持 welltypeid 为空，取所有
            sql = @"select ID,
       u.username,
       t.datafilename,
       r.validperiod,
       r.statusID,
       commentmessage,
       candownload,
       canview,
       createtime
  from resourcerequest r
  left join singlewell t
    on r.welldataid = t.dataid
  left join tempuser u
    on r.tempuserid = u.tempuserid
 where 1 = 1
   and r.statusID <> 0";
            if (!string.IsNullOrEmpty(wellTypeID))
            {
                sql = string.Concat(sql, "  and t.wellcatalogid = '" + wellTypeID, "'");
            }
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                return oDataSet.Tables[0];
            }
            else
            {
                return null;
            }
        }

        public bool GetPrivilege(string dataID, string userID, ref bool canView, ref bool canDownload)
        {
            bool rtnBool = false;
            string sql = string.Format("select 1 from resourcerequest r join tempuser t on r.tempuserid=t.tempuserid and r.validperiod>sysdate and t.expirationtime>sysdate and r.id='{0}' and r.tempuserid='{1}'  and r.statusid=1", dataID, userID);
            object rtnObj = this.DBHelper.ExecuteScalar(sql);
            if (rtnObj != null && int.Parse(rtnObj.ToString()) > 0)
            {
                rtnBool = true;
            }

            return rtnBool;
        }

        public bool HasNoExpiredRequest(string dataID, string userID)
        {
            bool rtnBool = false;
            string sql = string.Format("select count(*) from resourcerequest where tempuserid='{0}' and welldataid='{1}' and statusid='{2}' and validperiod>sysdate", userID, dataID, (int)ResourceRequestEnum.AgreeView);
            object rtnObj = this.DBHelper.ExecuteSql(sql);
            if (rtnBool != null && int.Parse(rtnObj.ToString()) > 0)
            {
                rtnBool = true;
            }
            return rtnBool;
        }

        public ResourceRequestModel GetResourceRequestModelByID(string id)
        {
            ResourceRequestModel rtnModel = null;
            string sql = string.Format("select * from resourcerequest where ID='{0}'", id);
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnModel = new ResourceRequestModel();
                rtnModel.RESOURCEREQUEST.Merge(oDataSet.Tables[0]);
            }

            return rtnModel;
        }

        public DataTable QueryAgent(ResourceRequestEnum requestEnum, int pageIndex, int pageSize, out int sum)
        {
            DataTable rtnDataTable = null;
            string dataSql = string.Empty;
            string countSql = string.Empty;
            if (requestEnum != ResourceRequestEnum.Expired)
            {
                dataSql = string.Format("select * from (select rownum rn, r.id, a.orgname, a.username, b.datafilename, r.validperiod, r.statusid, r.commentmessage, r.candownload, r.canview, r.createtime from resourcerequest r inner join tempuser a   on r.tempuserid = a.tempuserid inner join singlewell b   on r.welldataid = b.dataid where r.statusid = {0}   and r.validperiod > sysdate order by r.validperiod) m where m.rn between {1} and {2}",
                    (int)requestEnum, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
                countSql = string.Format("select count(*) from resourcerequest r inner join tempuser a   on r.tempuserid = a.tempuserid inner join singlewell b   on r.welldataid = b.dataid where r.statusid = {0}   and r.validperiod > sysdate ",
                    (int)requestEnum);
            }
            else
            {
                dataSql = string.Format("select * from (select rownum rn, r.id, a.orgname, a.username, b.datafilename, r.validperiod, r.statusid, r.commentmessage, r.candownload, r.canview, r.createtime from resourcerequest r inner join tempuser a   on r.tempuserid = a.tempuserid inner join singlewell b   on r.welldataid = b.dataid where   r.validperiod < sysdate  order by r.validperiod) m where m.rn between {0} and {1}",
                    (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
                countSql = string.Format("select count(*) from resourcerequest r inner join tempuser a   on r.tempuserid = a.tempuserid inner join singlewell b   on r.welldataid = b.dataid where   r.validperiod < sysdate");
            }
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(dataSql);
            sum = int.Parse(this.DBHelper.ExecuteScalar(countSql).ToString());
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        public DataTable QueryDone(int pageIndex, int pageSize, out int sum)
        {
            string sql = string.Format(@"select *
  from (select r.ID,
               u.username,
               t.datafilename,
                t.DataID,
               r.validperiod,
               r.statusID,
               commentmessage,
               candownload,
               canview,
               createtime,
               rownum rn
          from resourcerequest r
          inner join singlewell t
            on r.welldataid = t.dataid
          inner join tempuser u
            on r.tempuserid = u.tempuserid
         where 1 = 1
           and r.statusID <> 0
         order by r.validperiod desc) m
 where m.rn between {0} and {1}", (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);

            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            sum = int.Parse(this.DBHelper.ExecuteScalar(@"select count(*) from resourcerequest r
          inner join singlewell t
            on r.welldataid = t.dataid
          inner join tempuser u
            on r.tempuserid = u.tempuserid
         where 1 = 1
           and r.statusID <> 0").ToString());
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                return oDataSet.Tables[0];
            }
            else
            {
                return null;
            }
        }
    }

    public enum ResourceRequestEnum
    {
        /// <summary>
        /// 正在申请的
        /// </summary>
        NewRequest = 0,
        /// <summary>
        /// 允许浏览
        /// </summary>
        AgreeView = 1,
        /// <summary>
        /// 不允许浏览
        /// </summary>
        DisAgreeView = 2,
        /// <summary>
        /// 已经过期的
        /// </summary>
        Expired = 3
    }
}
